{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e98a7c3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_Excel数据提取_Vertical_人力资源.ipynb\n",
    "# Create By GF 2023-11-27 10:46"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7ecfcd1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3915ab64",
   "metadata": {},
   "outputs": [],
   "source": [
    "from Python3_DataProcFunc import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cac9b86e",
   "metadata": {},
   "outputs": [],
   "source": [
    "Entry = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"入职\")\n",
    "Depart = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"离职\")\n",
    "Profit = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"毛利\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b00c32dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列重命名: \n",
    "Rename_Entry = Entry\n",
    "Rename_Depart = Depart\n",
    "Rename_Profit = Profit\n",
    "# --------------------------------------------------\n",
    "Rename_Entry = Rename_Entry.rename(columns={\"事务日期\":\"日期\"})\n",
    "Rename_Depart = Rename_Depart.rename(columns={\"事务日期\":\"日期\"})\n",
    "Rename_Profit = Rename_Profit.rename(columns={\"业务职员代码\":\"职员代码\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4f64d149-2e0c-4b2a-9a2a-d803aff424c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>职员代码</th>\n",
       "      <th>日期</th>\n",
       "      <th>考核毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CQ1-2209008</td>\n",
       "      <td>2023-09-10</td>\n",
       "      <td>2489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>XA1-2210011</td>\n",
       "      <td>2023-09-22</td>\n",
       "      <td>779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GZ1-1103032</td>\n",
       "      <td>2023-07-29</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P2201-13</td>\n",
       "      <td>2023-09-14</td>\n",
       "      <td>3600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>YN1-1900001</td>\n",
       "      <td>2023-08-13</td>\n",
       "      <td>3269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187738</th>\n",
       "      <td>CQ1-2103016</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187739</th>\n",
       "      <td>CQ1-1803018</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187740</th>\n",
       "      <td>CQ1-2309022</td>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187741</th>\n",
       "      <td>CQ1-2309024</td>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187742</th>\n",
       "      <td>CQ1-2309025</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>-60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>187743 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               职员代码         日期  考核毛利\n",
       "0       CQ1-2209008 2023-09-10  2489\n",
       "1       XA1-2210011 2023-09-22   779\n",
       "2       GZ1-1103032 2023-07-29   299\n",
       "3          P2201-13 2023-09-14  3600\n",
       "4       YN1-1900001 2023-08-13  3269\n",
       "...             ...        ...   ...\n",
       "187738  CQ1-2103016 2023-09-30     0\n",
       "187739  CQ1-1803018 2023-09-30     0\n",
       "187740  CQ1-2309022 2023-09-28     0\n",
       "187741  CQ1-2309024 2023-09-28     0\n",
       "187742  CQ1-2309025 2023-09-30   -60\n",
       "\n",
       "[187743 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 筛选字段\n",
    "Filed_Name_Entry = [\"职员代码\", \"事务类别\", \"日期\", \"职位\"]\n",
    "Filed_Name_Depart = [\"职员代码\", \"事务类别\", \"日期\", \"职位\"]\n",
    "Filed_Name_Profit = [\"职员代码\", \"日期\", \"考核毛利\"]\n",
    "# --------------------------------------------------\n",
    "Filter_Entry = Rename_Entry[Filed_Name_Entry]\n",
    "Filter_Depart = Rename_Depart[Filed_Name_Depart]\n",
    "Filter_Profit = Rename_Profit[Filed_Name_Profit]\n",
    "# --------------------------------------------------\n",
    "Filter_Entry = Filter_Entry[Filter_Entry[\"事务类别\"] == \"职员入职\"]\n",
    "Filter_Entry = Filter_Entry[Filter_Entry[\"职位\"].str.contains(\"导购\")]\n",
    "# --------------------------------------------------\n",
    "Filter_Depart = Filter_Depart[Filter_Depart[\"事务类别\"] == \"职员离职\"]\n",
    "Filter_Depart = Filter_Depart[Filter_Depart[\"职位\"].str.contains(\"导购\")]\n",
    "Filter_Profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3175f865-85ac-4b6e-a849-9aef86428a17",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 类型调整\n",
    "ChangeType_Entry = Filter_Entry.copy()\n",
    "ChangeType_Depart = Filter_Depart.copy()\n",
    "# --------------------------------------------------\n",
    "ChangeType_Entry[\"日期\"] = ChangeType_Entry[\"日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")\n",
    "ChangeType_Depart[\"日期\"] = ChangeType_Depart[\"日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "278ba8f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>日均毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>481.540779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>444.082764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>534.941620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>519.575916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>464.990603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>479.636950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>541.106627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>545.998926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>950.756660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>774.451693</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>122 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期        日均毛利\n",
       "0   2023-06-01  481.540779\n",
       "1   2023-06-02  444.082764\n",
       "2   2023-06-03  534.941620\n",
       "3   2023-06-04  519.575916\n",
       "4   2023-06-05  464.990603\n",
       "..         ...         ...\n",
       "117 2023-09-26  479.636950\n",
       "118 2023-09-27  541.106627\n",
       "119 2023-09-28  545.998926\n",
       "120 2023-09-29  950.756660\n",
       "121 2023-09-30  774.451693\n",
       "\n",
       "[122 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 聚合数据\n",
    "Agg_Entry = ChangeType_Entry\n",
    "Agg_Depart = ChangeType_Depart\n",
    "Agg_Profit = Filter_Profit\n",
    "# --------------------------------------------------\n",
    "Agg_Entry = Agg_Entry[[\"日期\", \"事务类别\"]].groupby(\"日期\").count().reset_index().rename(columns={\"事务类别\":\"入职人数\"}) # -> 聚合\"入职\"表格。\n",
    "Agg_Depart = Agg_Depart[[\"日期\", \"事务类别\"]].groupby(\"日期\").count().reset_index().rename(columns={\"事务类别\":\"离职人数\"}) # -> 聚合\"离职\"表格。\n",
    "Agg_Profit = Agg_Profit[[\"日期\", \"考核毛利\"]].groupby(\"日期\").mean().reset_index().rename(columns={\"考核毛利\":\"日均毛利\"}) # -> 聚合\"毛利\"表格。\n",
    "Agg_Profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0763ea37-4ddb-4a27-b96b-16ca2344c100",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>日期</th>\n",
       "      <th>入职人数</th>\n",
       "      <th>离职人数</th>\n",
       "      <th>日均毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>5.417722</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.559524</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>5.417722</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>479.636950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>541.106627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>545.998926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>5.417722</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>950.756660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>5.417722</td>\n",
       "      <td>4.559524</td>\n",
       "      <td>774.451693</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>273 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date         日期      入职人数       离职人数        日均毛利\n",
       "0   2023-01-01 2023-01-01  6.000000  12.000000         NaN\n",
       "1   2023-01-02 2023-01-02  5.417722   1.000000         NaN\n",
       "2   2023-01-03 2023-01-03  3.000000   4.559524         NaN\n",
       "3   2023-01-04 2023-01-04  5.417722   3.000000         NaN\n",
       "4   2023-01-05 2023-01-05  1.000000   3.000000         NaN\n",
       "..         ...        ...       ...        ...         ...\n",
       "268 2023-09-26 2023-09-26  4.000000   2.000000  479.636950\n",
       "269 2023-09-27 2023-09-27  4.000000   3.000000  541.106627\n",
       "270 2023-09-28 2023-09-28  1.000000   3.000000  545.998926\n",
       "271 2023-09-29 2023-09-29  5.417722   2.000000  950.756660\n",
       "272 2023-09-30 2023-09-30  5.417722   4.559524  774.451693\n",
       "\n",
       "[273 rows x 5 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 连接查询\n",
    "LineTime = DataProcFunc_Pandas_Create_Date_Series_DataFrame(\"2023-01-01\", \"2023-09-30\")\n",
    "# --------------------------------------------------\n",
    "Joined = pd.merge(left=LineTime, right=Agg_Entry,  how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=Agg_Depart, how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=Agg_Profit, how=\"left\", on=\"日期\")\n",
    "Joined[\"入职人数\"] = Joined[\"入职人数\"].fillna(value=Joined[\"入职人数\"].mean())\n",
    "Joined[\"离职人数\"] = Joined[\"离职人数\"].fillna(value=Joined[\"离职人数\"].mean())\n",
    "#Joined[\"日均毛利\"] = Joined[\"日均毛利\"].fillna(value=Joined[\"日均毛利\"].mean())\n",
    "Joined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a05248a2-4437-4fc3-b38c-6e28db9eed45",
   "metadata": {},
   "outputs": [],
   "source": [
    "Joined[[\"日期\", \"入职人数\", \"离职人数\", \"日均毛利\"]].to_csv(\"./HR_Data_Entry_Depart.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6028b87f-47d4-4fb1-a046-599bb2e867fa",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
